﻿namespace DBUtil.SqlSegment
{
    /// <summary>
    /// 数据库字符串运算符，示例：
    /// <list type="number">
    /// <item>sqlserver: GetLength("name",true) => "len(isnull(name,''))"</item>
    /// <item>mysql: GetLength("name",true) => "length(ifnull(name,''))"</item>
    /// </list>
    /// </summary>
    public abstract class StringSqlSegment
    {
        public DBAccess db { get; }

        public StringSqlSegment(DBAccess db)
        {
            this.db = db;
        }

        /// <summary>
        /// 获取字符串的长度，示例：
        /// <list type="number">
        /// <item>sqlserver: GetLength("name",true) => "len(isnull(name,''))"</item>
        /// <item>mysql: GetLength("name",true) => "length(ifnull(name,''))"</item>
        /// </list>
        /// </summary>
        public abstract string GetLength(string sqlSeg, bool null2Zero = false);

        /// <summary>
        /// 去除列的字符串左右空格，示例：
        /// <list type="number">
        /// <item>sqlserver(&lt;2017): Trim("name") => "ltrim(rtrim(name))"</item>
        /// <item>sqlserver(>=2017): Trim("name") => "trim(name)"</item>
        /// <item>mysql: Trim("name") => "trim(name)"</item>
        /// </list>
        /// </summary>
        public abstract string Trim(string sqlSeg);

        /// <summary>
        /// 去除列的字符串左空格，示例：
        /// <list type="number">
        /// <item>sqlserver: LeftTrim("name") => "ltrim(name)"</item>
        /// <item>mysql: LeftTrim("name") => "ltrim(name)"</item>
        /// </list>
        /// </summary>
        public abstract string LeftTrim(string sqlSeg);

        /// <summary>
        /// 去除列的字符串右空格，示例：
        /// <list type="number">
        /// <item>sqlserver: RightTrim("name") => "rtrim(name)"</item>
        /// <item>mysql: RightTrim("name") => "rtrim(name)"</item>
        /// </list>
        /// </summary>
        public abstract string RightTrim(string sqlSeg);

        /// <summary>
        /// 转为大写，示例：
        /// <list type="number">
        /// <item>sqlserver: Upper("name") => "upper(name)"</item>
        /// <item>mysql: Upper("name") => "upper(name)"</item>
        /// </list>
        /// </summary>
        public abstract string Upper(string sqlSeg);

        /// <summary>
        /// 转为小写，示例：
        /// <list type="number">
        /// <item>sqlserver: Lower("name") => "lower(name)"</item>
        /// <item>mysql: Lower("name") => "lower(name)"</item>
        /// </list>
        /// </summary>
        public abstract string Lower(string sqlSeg);

        /// <summary>
        /// 字符串替换，示例：
        /// <list type="number">
        /// <item>sqlserver: ReplaceAll("name","'+'","'-'") => "replace(name,'+','-')"</item>
        /// <item>mysql: ReplaceAll("name","'+'","'-'") => "replace(name,'+','-')"</item>
        /// </list>
        /// </summary>
        public abstract string ReplaceAll(string inputSqlSeg, string oldStrsSqlSeg, string newStrSqlSeg);

        /* mysql SUBSTRING_INDEX
SELECT 
	SUBSTRING_INDEX('www.mysql.com', '.', 1) as c1, -- www
	SUBSTRING_INDEX('www.mysql.com', '.', 2) as c2, -- www.mysql
	SUBSTRING_INDEX('www.mysql.com', '.', 3) as c3, -- www.mysql.com
	SUBSTRING_INDEX('www.mysql.com', '.', 4) as c4, -- www.mysql.com
	
	SUBSTRING_INDEX('www.mysql.com', '.', -1) as c_1, -- com
	SUBSTRING_INDEX('www.mysql.com', '.', -2) as c_2, -- mysql.com
	SUBSTRING_INDEX('www.mysql.com', '.', -3) as c_3, -- www.mysql.com
	SUBSTRING_INDEX('www.mysql.com', '.', -4) as c_4  -- www.mysql.com

SELECT 
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 1) as c1, -- www
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 2) as c2, -- www^_^mysql
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 3) as c3, -- www^_^mysql^_^com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 4) as c4, -- www^_^mysql^_^com
	
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -1) as c_1, -- com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -2) as c_2, -- mysql^_^com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -3) as c_3, -- www^_^mysql^_^com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -4) as c_4  -- www^_^mysql^_^com
         */

        /* sqlserver reverse/substring/charindex
select 
	substring('www.myslq.com',0,charindex('.','www.myslq.com',0)) as c1, -- www
	reverse(substring(reverse('www.mysql.com'),0,charindex('.',reverse('www.mysql.com'),0))) as c_1 -- com

select	
	substring('www^_^myslq^_^com',0,CHARINDEX('^_^','www^_^myslq^_^com',0)) as c1,-- www
	reverse(substring(reverse('www^_^mysql^_^com'),0,charindex('^_^',reverse('www^_^mysql^_^com'),0))) as c_1 -- com
         */

        /// <summary>
        /// 按照指定字符串分割,取最开始的那个,示例:
        /// <list type="number">
        /// <item>sqlserver: SplitFirst("name","'-'") => "substring(name,0,charindex('-',name,0))"</item>
        /// <item>mysql: SplitFirst("name","'-'") => "substring_index(name, '-', 1)"</item>
        /// </list>
        /// </summary>
        public abstract string SplitFirst(string inputSqlSeg, string patternSqlSeg);

        /// <summary>
        /// 返按照指定字符串分割,取最后的那个,示例:
        /// <list type="number">
        /// <item>sqlserver: SplitLast("name","'-'") => "reverse(substring(reverse(name),0,charindex('-',reverse(name),0)))"</item>
        /// <item>mysql: SplitLast("name","'-'") => "substring_index(name, '-', -1)"</item>
        /// </list>
        /// </summary>
        public abstract string SplitLast(string inputSqlSeg, string patternSqlSeg);

        /// <summary>
        /// 字符串相加,示例:
        /// <list type="number">
        /// <item>sqlserver: Add("name","age") => "name+age"</item>
        /// <item>mysql: Add("name","age") => "concat_ws('',name,age)"</item>
        /// </list>
        /// </summary>
        public abstract string Add(string inputSqlSeg, string input2SqlSeg);

        /// <summary>
        /// 字符串截取,示例:
        /// <list type="number">
        /// <item>sqlserver: SubString("'abcde'",1,2) => ""</item>
        /// <item>mysql: SubString("'abcde'",1,2) => "substr('abcde',2,2)"</item>
        /// </list>
        /// </summary>
        /// <remarks>db中的字符索引从1开始,所以将start+1</remarks>
        public abstract string SubString(string sqlSeg, int start, int len);

        /// <summary>
        /// 字符串索引
        /// <list type="bullet">
        /// <item>mysql: IndexOf("name","'@'") => "(locate('@',name)-1)"</item>
        /// <item>mysql: IndexOf("name","'@'",false) => "(locate('@',binary name)-1)"</item>
        /// </list>
        /// </summary>
        /// <remarks>数据库中的索引从1开始,返回的时候会自动-1</remarks>
        public abstract string IndexOf(string inputSqlSeg, string patternSqlSeg, bool ignoreCase = true);

        /// <summary>
        /// 字符串是否为null或空字符串
        /// <list type="bullet">
        /// <item>mysql: IsNullOrEmpty("name") => "(name is null or length(name) = 0)"</item>
        /// </list>
        /// </summary>
        public abstract string IsNullOrEmpty(string inputSqlSeg);

        /// <summary>
        /// 字符串是否为null或空白字符串
        /// <list type="bullet">
        /// <item>mysql: IsNullOrEmpty("name") => "(name is null or length(trim(name)) = 0)"</item>
        /// </list>
        /// </summary>
        /// <remarks>注意: db中的空白字符串和c#中的可能会不一致, 如: c#认为'\t'是空白符,但mysql不这么认为</remarks>
        public abstract string IsNullOrWhiteSpace(string inputSqlSeg);
    }
}
